package com.coamc.xlsunit;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import javax.sql.DataSource;

import org.beetl.sql.core.ConnectionSource;
import org.beetl.sql.core.ConnectionSourceHelper;
import org.beetl.sql.core.DefaultNameConversion;
import org.beetl.sql.core.OnConnection;
import org.beetl.sql.core.SQLManager;
import org.beetl.sql.core.SQLManagerBuilder;
import org.beetl.sql.core.SQLReady;
import org.beetl.sql.core.db.ColDesc;
import org.beetl.sql.core.db.DBStyle;
import org.beetl.sql.core.db.TableDesc;
import org.beetl.sql.core.kit.CaseInsensitiveOrderSet;

/**
 * 直接使用BeetlSql来完成数据库访问
 * @author xiandafu
 *
 */
public class BeetlSqlDBAccess implements DBAccess {
	
	DataSource ds  = null;
	SQLManager sqlManager = null;
	public BeetlSqlDBAccess(DataSource ds,DBStyle dbStyle){
		this.ds = ds ;
		ConnectionSource source = ConnectionSourceHelper.getSingle(ds);
		SQLManagerBuilder smBuilder = SQLManager.newBuilder(source);
		//采用默认命名规则，列名就是map里的key的名字
		SQLManager sm = smBuilder.setDbStyle(dbStyle).setNc(new DefaultNameConversion()).build();
	}
	
	public BeetlSqlDBAccess(SQLManager sqlManager){
		this.sqlManager = sqlManager;
	}

	@Override
	public Map<String, Object> save(String tableName, Map<String, Object> row,List<String> id) {
		final TableDesc tableDesc = sqlManager.getMetaDataManager().getTable(tableName);
		StringBuilder sb = new StringBuilder("insert into ");
		sb.append(tableName);
		sb.append(" ( ");
		final Set<String> autos = new CaseInsensitiveOrderSet<String>();
		
		for(String col:row.keySet()){
			if(tableDesc.containCol(col)){
				if(tableDesc.getIdNames().contains(col)){
					//主键，需要重新取回来
					if(isAutoInc(tableDesc,col)&&!isNumber(row.get(col))){
						autos.add(col);
						continue;
					}
					
				}
				sb.append(col).append(",");
			}
			
		}
		//删除最后一个逗号
		sb.setLength(sb.length()-1);
		sb.append(") values (");
		List<Object> values = new ArrayList<Object>();
		final List<String> allCols = new ArrayList<String>();
		for(String col:row.keySet()){
			if(tableDesc.containCol(col)){
				if( tableDesc.getIdNames().contains(col)){
					if(isAutoInc(tableDesc,col)&&!isNumber(row.get(col))){
						continue;
					}
					
				}
				sb.append("?,");
				values.add(row.get(col));
				allCols.add(col);
			}
			
		}
		sb.setLength(sb.length()-1);
		sb.append(")");
		if(autos.size()>1){
			throw new RuntimeException("最多只支持一个自增主键");
		}
		final String sql = sb.toString();
		final Object[] paras = values.toArray();
		Object incColValue = sqlManager.executeOnConnection(new OnConnection<Object>(){
			@Override
			public Object call(Connection conn) throws SQLException {
				PreparedStatement ps = null;
				if(autos.isEmpty()){
					ps = conn.prepareStatement(sql);
				}else{
					//实际上只会有一个跟主键相关的自增主键
					String incColName = (String)autos.toArray()[0];
					ps = conn.prepareStatement(sql,new String[]{incColName});
				}
				 
				int i=1;
				for(Object obj:paras){
					String colName = allCols.get(i-1);
					ColDesc desc = tableDesc.getColDesc(colName);
					ps.setObject(i++, obj,desc.sqlType);
				}
				ps.executeUpdate();
				if(!autos.isEmpty()){
					ResultSet seqRs = ps.getGeneratedKeys();
	                seqRs.next();
	                Object key = seqRs.getObject(1);
	                seqRs.close();
	                return key;
				}else{
					return null;
				}
			}
			
		});
		//主键的值
		Map<String,Object> ret = new HashMap<String,Object>();
		for(String  key:tableDesc.getIdNames()){
			if(autos.contains(key)){
				ret.put(key, incColValue);
			}else{
				ret.put(key, row.get(key));
			}
		}
		
		System.out.println("准备数据 "+tableName  +" values "+row);
		return ret;
	}

	@Override
	public Map<String, Object> findById(String tableName, Map<String, Object> id) {
		StringBuilder sb = new StringBuilder("select * from ");
		sb.append(tableName).append(" where ");
		List<Object> values = new ArrayList<Object>();
		for(String key:id.keySet()){
			sb.append(key).append("=").append("? ").append(" and ");
			values.add(id.get(key));
		}
		//删除最后给and
		sb.setLength(sb.length()-4);
		List<Map> list = sqlManager.execute(new SQLReady(sb.toString(),values.toArray()), Map.class);
	    if(list.size()==0){
	    		return null;
	    }else{
	    		return  toExcelData(list.get(0));
	    }
	}

	@Override
	public List<Map<String,Object>> query(String tableName, String where, VariableTable vars) {
		StringBuilder sb = new StringBuilder("select * from ");
		sb.append(tableName).append(" where ").append(where);
		Map paras = new HashMap();
		paras.put("vars", vars);
		//参考BeetlSQLVarRef 如何获取excel变量
		List<Map>  list = sqlManager.execute(sb.toString(), Map.class, paras);
		//不能强制转化到 return List<Map<String, Object>>
		List<Map<String, Object>>  newList = new ArrayList<Map<String, Object>>();
		for(Map map:list){
			Map<String, Object> newMap = (Map<String, Object>)map;
			newList.add(newMap);
		}
		
		return toExcelData(newList);
	}
	
	
	private boolean isAutoInc(TableDesc desc,String id){
		ColDesc col = desc.getColDesc(id);
		return  col.isAuto;
	}
	
	private boolean isNumber(Object o){
		if(o==null){
			return false;
		}
		
		if(o instanceof Number){
			return true;
		}else{
			return false;
		}
	}
	
	private Map toExcelData(Map<String,Object> old ){
		Map map = new CaseInsensitiveHashMap();
		for(Entry<String,Object> entry:old.entrySet()) {
			String key=entry.getKey();
			String col = this.sqlManager.getNc().getColName(key);
			map.put(col, entry.getValue());
		}
		return map;
	}
	
	private List toExcelData(List<Map<String,Object>> list ){
		List newList = new ArrayList(list.size());
		for(Map<String,Object> map:list) {
			newList.add(toExcelData(map));
		}
		return newList;
	}

}
